﻿
Imports System.Data.SqlClient
Public Class clsPersonas
    Private mUsuario As String
    Private mPass As String
    Private mNombreCompleto As String
    Private mApellido As String
    Private mDNI As String
    Private mId_sector As Integer
    Private mSector As String
    Private mfechanac As Date
    Private mCodPostal As Short
    Private mLoc As String
    Private mTelefono As String
    Private mCelular As String
    Private mEmail As String
    Private mEstado As Integer



    Public Property USUARIO() As String

        Get
            Return mUsuario
        End Get
        Set(ByVal valor As String)
            If valor.Length <= 20 Then
                mUsuario = valor
            Else
                Throw New System.Exception("Usuario: Excedio un maximo de 20 caracteres")
            End If

        End Set
    End Property

    Public Property PASS() As String

        Get
            Return mPass
        End Get
        Set(ByVal valor As String)
            If valor.Length <= 10 Then
                mPass = valor
            Else
                Throw New System.Exception("Pass: Excedio un maximo de 10 caracteres")
            End If

        End Set
    End Property
    '/// NOMBRE COMPLETO MAX (40 CARACTERES)
    Public Property NOMBRES() As String

        Get
            Return mNombreCompleto
        End Get
        Set(ByVal valor As String)
            If valor.Length <= 30 Then
                mNombreCompleto = valor
            Else
                Throw New System.Exception("NombreCompleto: Excedio un maximo de 30 caracteres")
            End If

        End Set
    End Property


    Public Property APELLIDO() As String
        Get
            Return mApellido
        End Get
        Set(ByVal valor As String)
            If valor.Length <= 30 Then
                mApellido = valor
            Else
                Throw New System.Exception("Domicilio: Excedio un maximo de 30 caracteres")
            End If

        End Set
    End Property

    Public Property DNI() As String
        Get
            Return mDNI
        End Get
        Set(ByVal valor As String)
            If valor.Length <= 8 Then
                mDNI = valor
            Else
                Throw New System.Exception("DNI: Excedio un maximo de 8 caracteres")
            End If

        End Set
    End Property
    '/// NOMBRE COd POSTAL (1000-9999)
    Public Property ID_SECTOR() As Integer
        Get
            Return mId_sector
        End Get
        Set(ByVal valor As Integer)
            If valor >= 0 And valor <= 9999 Then
                mId_sector = valor
            Else
                Throw New System.Exception("Id_sector: Rango Invalido (0-9999)")
            End If
        End Set
    End Property


    Public Property SECTOR() As String
        Get
            Return mSector
        End Get
        Set(ByVal valor As String)
            If valor.Length <= 30 Then
                mSector = valor
            Else
                Throw New System.Exception("Sector: Excedio un maximo de 30 caracteres")
            End If

        End Set
    End Property

    '/// FECHA NAC 
    Public Property FECHANAC() As Date

        Get
            Return mfechanac
        End Get
        Set(ByVal valor As Date)
            mfechanac = valor
        End Set
    End Property


    '/// NOMBRE COd POSTAL (1000-9999)
    Public Property CP() As Short
        Get
            Return mCodPostal
        End Get
        Set(ByVal valor As Short)
            If valor >= 1000 And valor <= 9999 Then
                mCodPostal = valor
            Else
                Throw New System.Exception("Cod_Postal: Rango Invalido (1000-9999)")
            End If
        End Set
    End Property


    Public Property LOCALIDAD() As String
        Get
            Return mLoc
        End Get
        Set(ByVal value As String)
            If value.Length <= 30 Then
                mLoc = value
            Else
                Throw New Exception("LOCALIDAD: caracteres maximos 30")
            End If

        End Set
    End Property

    '/// TELEFONO RANGO (<=20)
    Public Property TEL() As String
        Get
            Return mTelefono
        End Get
        Set(ByVal valor As String)
            If valor.Length <= 20 Then
                mTelefono = valor
            Else
                Throw New System.Exception("Telefono: Debe tener un minimo de 8 digitos y un maximo de 10")
            End If

        End Set
    End Property


    '/// EMAIL
    Public Property MAIL() As String
        Get
            Return mEmail
        End Get

        Set(ByVal valor As String)
            mEmail = valor
        End Set
    End Property


    Public Property ESTADO() As Boolean
        Get
            Return mEstado
        End Get
        Set(ByVal valor As Boolean)
            If valor = True Then
                mEstado = True
            Else 'entonces INACTIVo
                mEstado = False
            End If
        End Set

    End Property


    Public Function Eliminar_usuario(ByVal pUsuario As String) As Boolean

        Dim conexion As New SqlConnection(My.Resources.RUTA)
        Dim comando As New SqlCommand


        Try
            conexion.Open() 'abro la conexion
            comando.Connection = conexion
            comando.CommandType = CommandType.StoredProcedure
            comando.CommandText = "sp_borrar_usuario"


            comando.Parameters.Clear() 'borro los parametros
            comando.Parameters.AddWithValue("@usuario", pUsuario)

            ' /// DEVUELVO TRUE SI SE ELIMINO EL REGISTRO
            Return Convert.ToBoolean(comando.ExecuteNonQuery)

        Catch ex As Exception
            Throw New Exception(ex.ToString)
        Finally
            conexion.Close()
            comando.Dispose()
            conexion.Dispose()
        End Try

    End Function

    Public Function Eliminar_Persona(ByVal pUsuario As String) As Boolean

        Dim conexion As New SqlConnection(My.Resources.RUTA)
        Dim comando As New SqlCommand


        Try
            conexion.Open() 'abro la conexion
            comando.Connection = conexion
            comando.CommandType = CommandType.StoredProcedure
            comando.CommandText = "sp_borrar_personas"


            comando.Parameters.Clear() 'borro los parametros
            comando.Parameters.AddWithValue("@usuario", pUsuario)

            ' /// DEVUELVO TRUE SI SE ELIMINO EL REGISTRO
            Return Convert.ToBoolean(comando.ExecuteNonQuery)

        Catch ex As Exception
            Throw New Exception(ex.ToString)
        Finally
            conexion.Close()
            comando.Dispose()
            conexion.Dispose()
        End Try

    End Function


    Public Function TraerSector(ByVal usuario As String) As Int32
        Dim conexion As New SqlConnection(My.Resources.RUTA)
        Dim comando As New SqlCommand
        Dim ultimo As Int16
        Try
            conexion.Open()
            comando.Connection = conexion
            comando.CommandType = CommandType.Text
            comando.CommandText = "select id_sector from personas where usuario='" & usuario & "'"
            ultimo = Convert.ToInt32(comando.ExecuteScalar)
            Return ultimo

        Catch ex As Exception
            Throw New Exception(ex.ToString)
        Finally
            conexion.Close()
            comando.Dispose()
            conexion.Dispose()
        End Try

    End Function

    Public Function Modificar_persona(ByVal pPerona As clsPersonas) As Boolean
        Dim conexion As New SqlConnection(My.Resources.RUTA)
        Dim comando As New SqlCommand
        Try
            conexion.Open()
            comando.Connection = conexion
            comando.CommandType = CommandType.StoredProcedure
            comando.CommandText = "sp_persona_modificar"
            comando.Parameters.Clear()
            With comando.Parameters
                .AddWithValue("@usuario", pPerona.USUARIO)
                .AddWithValue("@nom", pPerona.NOMBRES)
                .AddWithValue("@ape", pPerona.APELLIDO)
                .AddWithValue("@dni", pPerona.DNI)
                .AddWithValue("@tel", pPerona.TEL)
                .AddWithValue("@mail", pPerona.MAIL)
                .AddWithValue("@id_sector", pPerona.ID_SECTOR)
                .AddWithValue("@estado", pPerona.ESTADO)
            End With

            Return Convert.ToBoolean(comando.ExecuteNonQuery())

        Catch ex As Exception
            Throw New Exception(ex.ToString)
        Finally
            conexion.Close()
            comando.Dispose()
            conexion.Dispose()
        End Try

    End Function


    Public Function Modificar_usuario(ByVal pPerona As clsPersonas) As Boolean
        Dim conexion As New SqlConnection(My.Resources.RUTA)
        Dim comando As New SqlCommand
        Try
            conexion.Open()
            comando.Connection = conexion
            comando.CommandType = CommandType.StoredProcedure
            comando.CommandText = "sp_usuario_modificar"
            comando.Parameters.Clear()
            With comando.Parameters
                .AddWithValue("@usuario", pPerona.USUARIO)
                .AddWithValue("@pass", pPerona.PASS)
            End With

            Return Convert.ToBoolean(comando.ExecuteNonQuery())

        Catch ex As Exception
            Throw New Exception(ex.ToString)
        Finally
            conexion.Close()
            comando.Dispose()
            conexion.Dispose()
        End Try



    End Function

    '/////// CONSTRUCTOR////////////    
    Public Sub New()
        MyBase.New()
    End Sub
    ' ///////// DESTRUCTOR ///////// clase heredable
    Public Sub Dispose()
        MyBase.Finalize()
    End Sub
End Class
